package com.wxp.app.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.wxp.app.entity.UserInfo;

import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据库帮助类
 */
public class UserDBHelper extends SQLiteOpenHelper {

    /**
     * 标记
     */
    private static final String TAG = "UserDBHelper";
    /**
     * 数据库的名称
     */
    private static final String DB_NAME = "user.db";
    /**
     * 数据库的版本号
     */
    private static final int DB_VERSION = 1;
    /**
     * 数据库帮助器的实例
     */
    private static UserDBHelper mHelper = null;
    /**
     * 数据库的实例
     */
    private SQLiteDatabase mDB = null;
    /**
     * 表的名称
     */
    public static final String TABLE_NAME = "user_info";

    private UserDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }


    private UserDBHelper(Context context, int version) {
        super(context, DB_NAME, null, version);
    }

    /**
     * 利用单例模式获取数据库帮助器的唯一实例
     *
     * @param context 环境对象
     * @param version 数据库版本
     * @return 帮助器对象
     */
    public static UserDBHelper getInstance(Context context, int version) {
        if (version > 0 && mHelper == null) {
            mHelper = new UserDBHelper(context, version);
        } else if (mHelper == null) {
            mHelper = new UserDBHelper(context);
        }
        return mHelper;
    }

    /**
     * 打开数据库的读连接
     *
     * @return 数据对象
     */
    public SQLiteDatabase openReadLink() {
        if (mDB == null || !mDB.isOpen()) {
            mDB = mHelper.getReadableDatabase();
        }
        return mDB;
    }

    /**
     * 打开数据库的写连接
     *
     * @return 数据库对象
     */
    public SQLiteDatabase openWriteLink() {
        if (mDB == null || !mDB.isOpen()) {
            mDB = mHelper.getWritableDatabase();
        }
        return mDB;
    }

    /**
     * 关闭数据库连接
     */
    public void closeLink() {
        if (mDB != null && mDB.isOpen()) {
            mDB.close();
            mDB = null;
        }
    }

    /**
     * 创建数据库，执行建表语句
     *
     * @param db 数据库对象
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d(TAG, "onCreate");
        String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
        //判断表是否存在
        try (Cursor cursor = db.rawQuery("select * from " + TABLE_NAME, null);) {
            Log.d("message", TABLE_NAME + "表已存在！");
        } catch (Exception e) {
            Log.d("message", TABLE_NAME + "表不存在-开始创建表！");
            String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                    + "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                    + "name VARCHAR NOT NULL,"
                    + "age INTEGER NOT NULL,"
                    + "height INTEGER NOT NULL,"
                    + "weight FLOAT NOT NULL,"
                    + "married INTEGER NOT NULL,"
                    + "phone VARCHAR,"
                    + "password VARCHAR,"
                    + ");";
            Log.d(TAG, "create_sql:" + create_sql);
            // 执行完整的SQL语句
            db.execSQL(create_sql);
        }
    }

    /**
     * 升级数据库，执行表结构变更语句
     *
     * @param db         数据库对象
     * @param oldVersion 旧版本号
     * @param newVersion 新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade oldVersion=" + oldVersion + ", newVersion=" + newVersion);
        if (newVersion <= 1) {
            return;
        }
        //Android的ALTER命令不支持一次添加多列，只能分多次添加
        String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN create_by VARCHAR;";
        Log.d(TAG, "alter_sql:" + alter_sql);
        db.execSQL(alter_sql);
        alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN create_time VARCHAR; ";
        Log.d(TAG, "alter_sql:" + alter_sql);
        alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN update_by VARCHAR; ";
        Log.d(TAG, "alter_sql:" + alter_sql);
        alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN update_time VARCHAR; ";
        Log.d(TAG, "alter_sql:" + alter_sql);
        //执行完整的SQL语句
        db.execSQL(alter_sql);
    }

    /**
     * 根据指定条件删除表记录
     *
     * @param condition 条件
     * @return 影响条数
     */
    public int delete(String condition) {
        // 执行删除记录动作，该语句返回删除记录的数目
        return mDB.delete(TABLE_NAME, condition, null);
    }

    /**
     * 删除该表的所有记录
     *
     * @return 影响条数
     */
    public int deleteAll() {
        // 执行删除记录动作，该语句返回删除记录的数目
        return mDB.delete(TABLE_NAME, "1=1", null);
    }

    /**
     * 往该表添加一条记录
     *
     * @param info 用户对象
     * @return 主键id
     */
    public long insert(UserInfo info) {
        List<UserInfo> infoList = new ArrayList<>();
        infoList.add(info);
        return insert(infoList);
    }

    /**
     * 往该表添加多条记录
     *
     * @param infoList 用户列表
     * @return 主键id
     */
    public long insert(List<UserInfo> infoList) {
        long result = -1;
        for (int i = 0; i < infoList.size(); i++) {
            UserInfo info = infoList.get(i);
            List<UserInfo> tempList;
            // 如果存在同名记录，则更新记录
            // 注意条件语句的等号后面要用单引号括起来
            if (info.name != null && info.name.length() > 0) {
                String condition = String.format("name='%s'", info.name);
                tempList = query(condition);
                if (tempList.size() > 0) {
                    update(info, condition);
                    result = tempList.get(0).rowid;
                    continue;
                }
            }
            // 如果存在同样的手机号码，则更新记录
            if (info.phone != null && info.phone.length() > 0) {
                String condition = String.format("phone='%s'", info.phone);
                tempList = query(condition);
                if (tempList.size() > 0) {
                    update(info, condition);
                    result = tempList.get(0).rowid;
                    continue;
                }
            }
            // 不存在唯一性重复的记录，则插入新记录
            ContentValues cv = new ContentValues();
            cv.put("name", info.name);
            cv.put("age", info.age);
            cv.put("height", info.height);
            cv.put("weight", info.weight);
            cv.put("married", info.married);
            cv.put("phone", info.phone);
            cv.put("password", info.password);
            cv.put("create_by", info.createBy);
            cv.put("create_time", info.createTime);
            cv.put("update_by", info.updateBy);
            cv.put("update_time", info.updateTime);
            // 执行插入记录动作，该语句返回插入记录的行号
            result = mDB.insert(TABLE_NAME, "", cv);
            if (result == -1) {
                // 添加成功则返回行号，添加失败则返回-1
                return result;
            }
        }
        return result;
    }

    /**
     * 根据条件更新指定的表记录
     *
     * @param info
     * @param condition
     * @return
     */
    public int update(UserInfo info, String condition) {
        ContentValues cv = new ContentValues();
        cv.put("name", info.name);
        cv.put("age", info.age);
        cv.put("height", info.height);
        cv.put("weight", info.weight);
        cv.put("married", info.married);
        cv.put("phone", info.phone);
        cv.put("password", info.password);
        cv.put("create_by", info.createBy);
        cv.put("create_time", info.createTime);
        cv.put("update_by", info.updateBy);
        cv.put("update_time", info.updateTime);
        // 执行更新记录动作，该语句返回更新的记录数量
        return mDB.update(TABLE_NAME, cv, condition, null);
    }


    public int update(UserInfo info) {
        // 执行更新记录动作，该语句返回更新的记录数量
        return update(info, "rowid=" + info.rowid);
    }

    /**
     * 根据指定条件查询记录，并返回结果数据列表
     *
     * @param condition
     * @return
     */
    public List<UserInfo> query(String condition) {
        String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time," +
                "phone,password from %s where %s;", TABLE_NAME, condition);
        Log.d(TAG, "query sql: " + sql);
        List<UserInfo> infoList = new ArrayList<>();
        // 执行记录查询动作，该语句返回结果集的游标
        Cursor cursor = mDB.rawQuery(sql, null);
        // 循环取出游标指向的每条记录
        while (cursor.moveToNext()) {
            UserInfo info = new UserInfo();
            // 取出长整型数
            info.rowid = cursor.getLong(0);
            // 取出整型数
            info.xuhao = cursor.getInt(1);
            // 取出字符串
            info.name = cursor.getString(2);
            // 取出整型数
            info.age = cursor.getInt(3);
            // 取出长整型数
            info.height = cursor.getLong(4);
            // 取出浮点数
            info.weight = cursor.getFloat(5);
            //SQLite没有布尔型，用0表示false，用1表示true
            info.married = (cursor.getInt(6) == 0) ? false : true;
            // 取出字符串
            info.phone = cursor.getString(7);
            info.password = cursor.getString(8);
            info.createBy = cursor.getString(9);
            info.createTime = cursor.getString(10);
            info.updateBy = cursor.getString(11);
            info.updateTime = cursor.getString(12);
            //添加数据
            infoList.add(info);
        }
        // 查询完毕，关闭数据库游标
        cursor.close();
        return infoList;
    }

}
